1. Field of the Invention
The present invention relates to the process of accessing a table within a database system. More specifically, the present invention relates to a method and an apparatus for using a hash-partitioned index to access a table that is not hash-partitioned.
2. Related Art
Many database applications use automatically generated keys as column values in relation tables. In such applications, the automatically generated key typically has a monotonically increasing value. For example, the transaction identifier in an Online Transaction Processing (OLTP) environment is usually an automatically generated key that is incremented to generate successive key values. Similarly, surrogate keys that are used in star schemas of data warehousing environments also tend to be automatically generated with monotonically increasing values.
Often, these automatically generated keys with monotonically increasing values have indexes defined on them. As a result, index accesses and maintenance activities tend to occur in a highly localized area of the index (e.g., the right-most edge of the index). These localized areas are known as “hotspots.” A hotspot can cause severe resource contention during periods of increased database activity, which can result in a performance degradation of the database system, such as, an increased response time for database transactions and reduced throughput.
Two methods are presently used to reduce hotspots. The first method reverses the bytes in the key and then uses the reversed key to perform operations in the index. This method largely eliminates the formation of hotspots because it disperses the reversed keys across the whole index. One of the drawbacks of this approach is that since it disperses the reversed keys in a highly random fashion, it causes continuous random movement of the disk head, which causes the disk seek-time to increase considerably because locality-of-reference is lost. Thus, although this method eliminates hotspots, it may, in fact, degrade the overall performance of the system. The second method is to use hash-partitioned tables, in which rows are mapped into partitions based on applying the hash function to the partitioning key. Since each partition of a hash-partitioned table has its own index, the index accesses and maintenance activities are equally distributed among all the partitions, thereby eliminating the formation of a single large hotspot. The drawback of this method is that it forces the user to partition tables using the hash-partitioning technique, which may not be the optimal partitioning technique for certain database applications. For example, in OLTP applications, it is quite common to use range-partitioned tables that are partitioned using the date fields for simplifying database management and for improving database performance. Thus, for such applications, we cannot use hash-partitioning to eliminate hotspots.
Hence, what is needed is a method and an apparatus for accessing a table that does not have the above-described drawbacks of the existing techniques.